﻿using DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using DBUtility;

namespace DAL
{
    public class DeliveryDAL
    {
        /// <summary>
        /// 获取总成列表
        /// </summary>
        public DataSet GetDeliveryMain()
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select ROW_NUMBER() OVER (ORDER BY DInterID)Num,DInterID, ");
            commandText.Append(" CONVERT(varchar(100), Ddatetime, 23)Fdate,b.NAME FBillerID ");
            commandText.Append(" from t_deliveryMain left join sys_master b on b.ID = DmaikingID ");
            commandText.Append(" where Dstates=2 order by DInterID ");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// 获取总成单里的明细
        /// </summary>
        public DataSet GetDelivery(string DInterID)
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select ROW_NUMBER() OVER (ORDER BY Did)Num,Did,DSKU,Dnumber,NDnumber ");
            commandText.Append(" from  t_delivery ");
            commandText.Append(" where Dstates = 1 ");
            commandText.Append(" and DInterID =  '" + DInterID + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// 获取总成已扫描明细
        /// </summary>
        public DataSet GetDeliveryStk(string Did)
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select ROW_NUMBER() OVER (ORDER BY PkgsID)Num,PkgsID,ItemSku,StickLen,Stick,FQty,a.FName ");
            commandText.Append(" from stk_Package left join t_StockPlace a on a.FSPID = PosiID ");
            commandText.Append(" where DeliveryBillNo =  '" + Did + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// 获取明细信息
        /// </summary>
        public bool GetDeliveryById(string Did, out string DInterID, out string Dnumber, out string NDnumber)
        {
            bool lb_return = false;
            DataSet ds = new DataSet();
            DInterID = ""; Dnumber = ""; NDnumber = ""; 

            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select DInterID,Dnumber,NDnumber ");
            commandText.Append(" from t_delivery ");
            commandText.Append(" where Did ='" + Did + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (ds != null)
            {
                lb_return = true;

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    DInterID = dr[0].ToString().Trim();
                    Dnumber = dr[1].ToString().Trim();
                    NDnumber = dr[2].ToString().Trim(); 
                }
            }

            return lb_return;
        }
       
        /// <summary>
        /// 扫码获取件信息
        /// </summary>
        public bool GetPackage(string PkgsBarCode, out string PkgsID, out string ItemSku, out string StickLen, out string Stick, out string FQty, out string FNumber, out string PkgsStatus)
        {
            bool lb_return = false;
            DataSet ds = new DataSet();
            PkgsID = ""; ItemSku = ""; StickLen = ""; Stick = ""; FQty = ""; FNumber = ""; PkgsStatus = "";

            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select PkgsID,ItemSku,StickLen,Stick,FQty,a.FNumber,PkgsStatus");
            commandText.Append(" from stk_Package left join t_StockPlace a on a.FSPID = PosiID ");
            commandText.Append(" where PkgsBarCode =  '" + PkgsBarCode + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (ds != null)
            {
                lb_return = true;

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    PkgsID = dr[0].ToString().Trim();
                    ItemSku = dr[1].ToString().Trim();
                    StickLen = dr[2].ToString().Trim();
                    Stick = dr[3].ToString().Trim();
                    FQty = dr[4].ToString().Trim();
                    FNumber = dr[5].ToString().Trim();
                    PkgsStatus = dr[6].ToString().Trim();
                }
            }

            return lb_return;
        }
        /// <summary>
        /// 提交扫描明细
        /// </summary>
        public bool UpdateDeliveryStk(string PkgsID, string Did) 
        {
            bool lb_return = false;
            List<string> sql = new List<string>();

            StringBuilder commandText1 = new StringBuilder();
            commandText1.Append(" update stk_Package set DeliveryBillNo='" + Did + "'");
            commandText1.Append(" where PkgsID='" + PkgsID + "'");

            StringBuilder commandText2 = new StringBuilder();
            commandText2.Append(" update t_delivery set NDnumber = ");
            commandText2.Append("(select  SUM(a.FQty) from stk_Package a where a.DeliveryBillNo=Did) ");
            commandText2.Append(" where Did = '" + Did + "'");

            sql.Add(commandText1.ToString());
            sql.Add(commandText2.ToString());

            try
            {
                int i = DbHelperSQL.ExecuteSqlTran(sql);
                if (i > 0)
                {
                    lb_return = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return lb_return;
        }
        /// <summary>
        /// 完成明细
        /// </summary>
        public bool FinishDelivery(string Did)
        {
            bool lb_return = false;
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" update t_delivery set Dstates=2 where Did=" + Did);
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                int i = DbHelperSQL.ExecuteSql(commandText.ToString());
                if (i > 0)
                { lb_return = true; }
                else { lb_return = false; }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lb_return;
        }
        /// <summary>
        /// 完成订单
        /// </summary>
        public bool FinishDeliveryMain(string DInterID)
        {
            bool lb_return = false;
            List<string> sql = new List<string>();
            StringBuilder commandText1 = new StringBuilder();
            commandText1.Append(" update t_deliveryMain set Dstates=3 where DInterID='" + DInterID + "' ");

            StringBuilder commandText2 = new StringBuilder();
            commandText2.Append(" update t_delivery set Dstates=2 where DInterID='" + DInterID + "' ");

            StringBuilder commandText3 = new StringBuilder();
            commandText3.Append(" update stk_Package set PkgsStatus=5 ");
            commandText3.Append(" where DeliveryBillNo in( select Did from t_delivery where DInterID='" + DInterID + "') ");

            sql.Add(commandText1.ToString());
            sql.Add(commandText2.ToString());
            sql.Add(commandText3.ToString());

            try
            {
                int i = DbHelperSQL.ExecuteSqlTran(sql);
                if (i > 0)
                {
                    lb_return = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return lb_return;
        }


    }
}
